﻿





-- 人员卡信息查询
create procedure [dbo].[RyCaInfo_Get]
@lsh bigint = null,
@sfzh varchar(18) = null,
@kh varchar(18)=null,
@fksjks datetime = null,
@fksjjs datetime = null,
@cxsjks datetime = null,
@cxsjjs datetime = null,
@xm varchar(10)=null,
@kbs varchar(20) = null,
@RowCount int =200
as
set rowcount @RowCount

select lsh,RyCa_Info.sfzh,RyCa_Info.kh,fksj,cxsj,xm,kbs
from RyCa_Info 
inner join Ry_Info on(RyCa_Info.sfzh=Ry_Info.sfzh)
inner join Ca_Info on(RyCa_Info.kh = Ca_Info.kh)
where (@lsh is null or lsh=@lsh) and
(@sfzh is null or RyCa_Info.sfzh like @sfzh+'%') and
(@kh is null or RyCa_Info.kh=@kh) and
(@fksjks is null or fksj>=@fksjks) and
(@fksjjs is null or fksj<@fksjjs) and
(@cxsjks is null or cxsj>=@cxsjks) and
(@cxsjjs is null or cxsj<@cxsjjs) and
(@xm is null or xm like '%'+@xm+'%') and
(@kbs is null or kbs like @kbs+'%')
order by fksj desc,cxsj

select ckjl+ykjl total,ckjl,ykjl from
(
select count(1) ckjl
from RyCa_Info 
inner join Ry_Info on(RyCa_Info.sfzh=Ry_Info.sfzh)
inner join Ca_Info on(RyCa_Info.kh = Ca_Info.kh)
where (@lsh is null or lsh=@lsh) and
(@sfzh is null or RyCa_Info.sfzh like @sfzh+'%') and
(@kh is null or RyCa_Info.kh=@kh) and
(@fksjks is null or fksj>=@fksjks) and
(@fksjjs is null or fksj<@fksjjs) and
(@cxsjks is null or cxsj>=@cxsjks) and
(@cxsjjs is null or cxsj<@cxsjjs) and
(@xm is null or xm like '%'+@xm+'%') and
(@kbs is null or kbs like @kbs+'%') and cxsj is null
) aa inner join
(
select count(1) ykjl
from RyCa_Info 
inner join Ry_Info on(RyCa_Info.sfzh=Ry_Info.sfzh)
inner join Ca_Info on(RyCa_Info.kh = Ca_Info.kh)
where (@lsh is null or lsh=@lsh) and
(@sfzh is null or RyCa_Info.sfzh like @sfzh+'%') and
(@kh is null or RyCa_Info.kh=@kh) and
(@fksjks is null or fksj>=@fksjks) and
(@fksjjs is null or fksj<@fksjjs) and
(@cxsjks is null or cxsj>=@cxsjks) and
(@cxsjjs is null or cxsj<@cxsjjs) and
(@xm is null or xm like '%'+@xm+'%') and
(@kbs is null or kbs like @kbs+'%') and cxsj is not null
) bb on(1=1)

set rowcount 0